package com.wande.dataplatform.filecollection.parser.impl;

import cn.hutool.core.util.StrUtil;
import com.wande.dataplatform.filecollection.common.enums.FileCollectionErrorCode;
import com.wande.dataplatform.filecollection.common.exception.FileCollectionException;
import com.wande.dataplatform.filecollection.domain.dto.FieldInfo;
import com.wande.dataplatform.filecollection.parser.AbstractFileParser;
import com.wande.dataplatform.filecollection.parser.ParserConfig;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.stereotype.Component;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.function.Consumer;

/**
 * Excel文件解析器
 * 支持XLS和XLSX格式
 * 支持多工作表解析和指定起始行
 * 支持流式解析大文件
 *
 * @author dataplatform
 * @date 2025-01-24
 */
@Slf4j
@Component
public class ExcelFileParser extends AbstractFileParser {

    private static final int SAMPLE_SIZE = 100;

    @Override
    public String getSupportedFileType() {
        return "XLS,XLSX";
    }

    @Override
    public List<FieldInfo> detectFields(InputStream inputStream, ParserConfig config) {
        validateInputStream(inputStream);
        validateConfig(config);

        List<FieldInfo> fields = new ArrayList<>();

        try (Workbook workbook = createWorkbook(inputStream)) {
            Sheet sheet = getSheet(workbook, config);
            if (sheet == null) {
                return fields;
            }

            int startRow = config.getStartRow() != null ? config.getStartRow() : 0;
            int headerRowIndex = startRow;

            // 读取表头
            Row headerRow = sheet.getRow(headerRowIndex);
            if (headerRow == null) {
                return fields;
            }

            List<String> headers = new ArrayList<>();
            for (Cell cell : headerRow) {
                String header = getCellValueAsString(cell);
                headers.add(cleanFieldName(header));
            }

            // 收集样本数据用于类型推断
            Map<Integer, List<String>> samples = new HashMap<>();
            int sampleCount = 0;
            int dataStartRow = config.getHasHeader() ? headerRowIndex + 1 : headerRowIndex;

            for (int i = dataStartRow; i <= sheet.getLastRowNum() && sampleCount < SAMPLE_SIZE; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }

                for (int j = 0; j < headers.size(); j++) {
                    Cell cell = row.getCell(j);
                    String value = getCellValueAsString(cell);
                    samples.computeIfAbsent(j, k -> new ArrayList<>()).add(value);
                }
                sampleCount++;
            }

            // 创建字段信息
            for (int i = 0; i < headers.size(); i++) {
                String fieldName = headers.get(i);
                List<String> fieldSamples = samples.getOrDefault(i, Collections.emptyList());
                String fieldType = inferFieldTypeFromSamples(fieldSamples);

                FieldInfo fieldInfo = createFieldInfo(fieldName, fieldType, i);
                if (!fieldSamples.isEmpty()) {
                    fieldInfo.setSampleValue(fieldSamples.get(0));
                }
                fields.add(fieldInfo);
            }

        } catch (IOException e) {
            log.error("Failed to detect Excel fields", e);
            throw new FileCollectionException(FileCollectionErrorCode.FILE_PARSE_ERROR, "Failed to detect Excel fields: " + e.getMessage());
        }

        return fields;
    }

    @Override
    public Iterator<Map<String, Object>> parse(InputStream inputStream, ParserConfig config) {
        validateInputStream(inputStream);
        validateConfig(config);

        try {
            Workbook workbook = createWorkbook(inputStream);
            Sheet sheet = getSheet(workbook, config);
            if (sheet == null) {
                throw new FileCollectionException(FileCollectionErrorCode.FILE_FORMAT_ERROR, "Sheet not found");
            }

            return new ExcelIterator(workbook, sheet, config);

        } catch (IOException e) {
            log.error("Failed to parse Excel file", e);
            throw new FileCollectionException(FileCollectionErrorCode.FILE_PARSE_ERROR, "Failed to parse Excel file: " + e.getMessage());
        }
    }

    @Override
    public void parseStream(InputStream inputStream, ParserConfig config, Consumer<Map<String, Object>> consumer) {
        validateInputStream(inputStream);
        validateConfig(config);

        try (Workbook workbook = createWorkbook(inputStream)) {
            Sheet sheet = getSheet(workbook, config);
            if (sheet == null) {
                throw new FileCollectionException(FileCollectionErrorCode.FILE_FORMAT_ERROR, "Sheet not found");
            }

            int startRow = config.getStartRow() != null ? config.getStartRow() : 0;
            int headerRowIndex = startRow;

            // 读取表头
            Row headerRow = sheet.getRow(headerRowIndex);
            if (headerRow == null) {
                return;
            }

            List<String> headers = new ArrayList<>();
            for (Cell cell : headerRow) {
                String header = getCellValueAsString(cell);
                headers.add(cleanFieldName(header));
            }

            // 读取数据行
            int dataStartRow = config.getHasHeader() ? headerRowIndex + 1 : headerRowIndex;
            int endRow = config.getEndRow() != null && config.getEndRow() > 0 
                    ? config.getEndRow() : sheet.getLastRowNum();

            for (int i = dataStartRow; i <= endRow; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }

                Map<String, Object> rowData = new LinkedHashMap<>();
                for (int j = 0; j < headers.size(); j++) {
                    Cell cell = row.getCell(j);
                    String value = getCellValueAsString(cell);
                    value = processFieldValue(value, config);
                    rowData.put(headers.get(j), value);
                }

                // 跳过空行
                if (config.getSkipEmptyRows() && isEmptyRow(rowData)) {
                    continue;
                }

                consumer.accept(rowData);
            }

        } catch (IOException e) {
            log.error("Failed to parse Excel file in stream mode", e);
            throw new FileCollectionException(FileCollectionErrorCode.FILE_PARSE_ERROR, "Failed to parse Excel file in stream mode: " + e.getMessage());
        }
    }

    @Override
    public boolean validate(InputStream inputStream, ParserConfig config) {
        try (Workbook workbook = createWorkbook(inputStream)) {
            Sheet sheet = getSheet(workbook, config);
            return sheet != null && sheet.getLastRowNum() >= 0;
        } catch (Exception e) {
            log.warn("Excel validation failed: {}", e.getMessage());
            return false;
        }
    }

    /**
     * 创建Workbook对象
     *
     * @param inputStream 输入流
     * @return Workbook对象
     * @throws IOException IO异常
     */
    private Workbook createWorkbook(InputStream inputStream) throws IOException {
        // 使用BufferedInputStream支持mark/reset
        if (!inputStream.markSupported()) {
            inputStream = new BufferedInputStream(inputStream);
        }

        inputStream.mark(8);
        try {
            // 尝试作为XLSX打开
            return new XSSFWorkbook(inputStream);
        } catch (Exception e) {
            // 重置流并尝试作为XLS打开
            try {
                inputStream.reset();
                return new HSSFWorkbook(inputStream);
            } catch (Exception ex) {
                throw new IOException("Unsupported Excel format", ex);
            }
        }
    }

    /**
     * 获取工作表
     *
     * @param workbook Workbook对象
     * @param config 解析器配置
     * @return Sheet对象
     */
    private Sheet getSheet(Workbook workbook, ParserConfig config) {
        if (StrUtil.isNotBlank(config.getSheetName())) {
            return workbook.getSheet(config.getSheetName());
        }

        int sheetIndex = config.getSheetIndex() != null ? config.getSheetIndex() : 0;
        if (sheetIndex >= 0 && sheetIndex < workbook.getNumberOfSheets()) {
            return workbook.getSheetAt(sheetIndex);
        }

        return null;
    }

    /**
     * 获取单元格值（字符串形式）
     *
     * @param cell 单元格
     * @return 单元格值
     */
    private String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    return sdf.format(cell.getDateCellValue());
                } else {
                    double numericValue = cell.getNumericCellValue();
                    // 如果是整数，不显示小数点
                    if (numericValue == Math.floor(numericValue)) {
                        return String.valueOf((long) numericValue);
                    }
                    return String.valueOf(numericValue);
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                try {
                    return String.valueOf(cell.getNumericCellValue());
                } catch (Exception e) {
                    return cell.getStringCellValue();
                }
            case BLANK:
                return "";
            default:
                return "";
        }
    }

    /**
     * Excel迭代器
     */
    private class ExcelIterator implements Iterator<Map<String, Object>> {
        private final Workbook workbook;
        private final Sheet sheet;
        private final ParserConfig config;
        private final List<String> headers;
        private int currentRowIndex;
        private final int endRow;

        public ExcelIterator(Workbook workbook, Sheet sheet, ParserConfig config) {
            this.workbook = workbook;
            this.sheet = sheet;
            this.config = config;
            this.headers = new ArrayList<>();

            int startRow = config.getStartRow() != null ? config.getStartRow() : 0;
            int headerRowIndex = startRow;

            // 读取表头
            Row headerRow = sheet.getRow(headerRowIndex);
            if (headerRow != null) {
                for (Cell cell : headerRow) {
                    String header = getCellValueAsString(cell);
                    headers.add(cleanFieldName(header));
                }
            }

            this.currentRowIndex = config.getHasHeader() ? headerRowIndex + 1 : headerRowIndex;
            this.endRow = config.getEndRow() != null && config.getEndRow() > 0 
                    ? config.getEndRow() : sheet.getLastRowNum();
        }

        @Override
        public boolean hasNext() {
            return currentRowIndex <= endRow;
        }

        @Override
        public Map<String, Object> next() {
            if (!hasNext()) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    log.warn("Failed to close workbook", e);
                }
                throw new NoSuchElementException();
            }

            Row row = sheet.getRow(currentRowIndex);
            currentRowIndex++;

            if (row == null) {
                return new LinkedHashMap<>();
            }

            Map<String, Object> rowData = new LinkedHashMap<>();
            for (int i = 0; i < headers.size(); i++) {
                Cell cell = row.getCell(i);
                String value = getCellValueAsString(cell);
                value = processFieldValue(value, config);
                rowData.put(headers.get(i), value);
            }

            return rowData;
        }
    }
}
